package com.situ.company.dao;

import com.situ.company.entity.Employee;
import com.situ.company.util.DBUtil;
import com.situ.company.util.StringUtil;
import com.situ.company.vo.EmployeeVO;

import java.util.List;

public class EmployeeDao {
    public void insert(Employee e){
        String sql="insert into employee (employee.name,age,sex,employee.tel,address,dept_id)values(?,?,?,?,?,?)";
        DBUtil.executeDML(sql,e.getName(),e.getAge(),e.getSex(),e.getTel(),e.getAddress(),e.getDeptId());
    }

    public List<EmployeeVO> list(Integer page, Integer limit, String name, String tel){
        String sql ="select Employee.*,dept.name 'deptName' from employee left join dept on dept.id=employee.dept_id where status = 0 ";/** sql语句重新写 右链接*/
        if(StringUtil.isNotBlank(name)&&StringUtil.isNotBlank(tel)) {
            sql += " and instr(employee.name,?)>0 and instr(employee.tel,?)>0";
            sql += " limit ?,?";
            return DBUtil.executeDQL(sql, EmployeeVO.class, name,tel,(page - 1) * limit, limit);
        }
        if(StringUtil.isNotBlank(tel)){
            sql += " and instr(employee.tel,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql, EmployeeVO.class, tel,(page - 1) * limit, limit);
        }
        if(StringUtil.isNotBlank(name)){
            sql += " and instr(employee.name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql, EmployeeVO.class, name,(page - 1) * limit, limit);
        }
        sql += " limit ?,?";
        return DBUtil.executeDQL(sql, EmployeeVO.class, (page - 1) * limit, limit);
    }


    public void delete(Integer id) {
        String sql ="update employee set status = -1 where id= ?";
        DBUtil.executeDML(sql,id);
    }

    public List<Employee> edit(Integer id) {
        String sql="select * from employee where id =? and status = 0";
        return DBUtil.executeDQL(sql,Employee.class,id);
    }

    public void update(Employee e) {
        String sql ="update employee set employee.name=?,age=?,sex=?,employee.tel=?,address=?,dept_id=? where id =? and status = 0";
        DBUtil.executeDML(sql,e.getName(),e.getAge(),e.getSex(),e.getTel(),e.getAddress(),e.getDeptId(),e.getId());
    }

    public Long count(String name, String tel) {
        String sql ="select count(1) from employee where status = 0";
        if(StringUtil.isNotBlank(name) && StringUtil.isNotBlank(tel)) {
            sql += " and instr(employee.name,?)>0 and instr(employee.tel,?)>0";

            return DBUtil.executeCount(sql, name,tel);
        }
        if(StringUtil.isNotBlank(name)){
            sql += " and instr(employee.name,?)>0 ";
            return DBUtil.executeCount(sql, name);
        }
        if(StringUtil.isNotBlank(tel)){
            sql += " and instr(employee.tel,?)>0 ";
            return DBUtil.executeCount(sql, tel);
        }
        return DBUtil.executeCount(sql);
    }
}
